﻿
Imports MySql.Data.MySqlClient   
Public Class MySqlUtilities
    Public mysqlhost As String
    Public mysqlport As Integer
    Public mysqldb As String
    Public mysqluser As String
    Public mysqlpsqwd As String
    Private errorcode As Int32
    Private errorstring As String
    Private cxbuilder As MySqlConnectionStringBuilder
    Sub New(ByVal conf As MySqlConfiguration)
        setConfig(conf)
        cxbuilder = New MySqlConnectionStringBuilder
    End Sub

    Sub New()
        setConfig(New MySqlConfiguration)
        cxbuilder = New MySqlConnectionStringBuilder
    End Sub

    Public Property MysqErrorcode As Int32
        Get
            Return errorcode
        End Get
        Set(ByVal value As Int32)
            errorcode = value
        End Set
    End Property

    Public Property MysqlErrorMsg As String
        Get
            Return errorstring
        End Get
        Set(ByVal value As String)
            errorstring = value
        End Set
    End Property

    Public Function pingMysql() As Boolean
        'ping the host
        Try
            Dim ping As New System.Net.NetworkInformation.Ping
            Dim resp As System.Net.NetworkInformation.PingReply = ping.Send(mysqlhost)
            If (Not resp.Status = Net.NetworkInformation.IPStatus.Success) Then
                Dim e As String = "Error Occured: " + resp.Status.ToString
                MysqErrorcode = 0
                MysqlErrorMsg = e
                Return False
            End If
        Catch ex As Exception
            Dim e As String = "Error Occured: " + ex.Message
            MysqErrorcode = 0
            MysqlErrorMsg = e
            Return False
        End Try
        'try to connect enven if db not exist, but the server exist, so ping is true
        If (Not testConnection()) Then
            Select Case MysqErrorcode
                Case MySqlErrorCode.UnknownDatabase
                    MysqlErrorMsg = "MySql Server is Alive and Connectable." + vbCrLf + "Database " + mysqldb + " not exist"
                Case MySqlErrorCode.AccessDenied
                    MysqlErrorMsg = "MySql Server is Alive and Connectable." + vbCrLf + "Bad Username or Password "
                Case MySqlErrorCode.DatabaseAccessDenied
                    MysqlErrorMsg = "MySql Server is Alive and Connectable." + vbCrLf + "Access to Database " + mysqldb + " denied"
                Case Else
                    MysqlErrorMsg = "Unknow error:" + MysqlErrorMsg
            End Select
            Return False
        End If
        Return True
    End Function

    Public Function databaseExist() As Boolean
        If (Not testConnection()) Then
            Select Case MysqErrorcode
                Case MySqlErrorCode.UnknownDatabase
                    MysqlErrorMsg = "Database " + mysqldb + " not found"
                Case MySqlErrorCode.AccessDenied
                    MysqlErrorMsg = "Bad Username or Password "
                Case MySqlErrorCode.DatabaseAccessDenied
                    MysqlErrorMsg = "Access to Database " + mysqldb + " denied"
                Case Else
                    MysqlErrorMsg = "Unknow error:" + MysqlErrorMsg
            End Select
            Return False
        End If
        Return True
    End Function

    Public Function testConnection() As Boolean
        Dim cx As New MySqlConnection(getConnectionString(True))
        Try
            cx.Open()
            cx.Close()
        Catch ex As MySqlException
            MysqErrorcode = ex.Number
            MysqlErrorMsg = ex.Message
            Return False
        End Try
        Return True
    End Function

    Public Sub setConfig(ByVal conf As MySqlConfiguration)
        mysqlhost = conf.mysql_adress
        If (Not String.IsNullOrEmpty(conf.mysql_database)) Then mysqldb = conf.mysql_database
        mysqlport = conf.mysql_port
        mysqlpsqwd = conf.mysql_password
        mysqluser = conf.mysql_user
    End Sub

    Public Function getConnectionString(ByVal idb As Boolean) As String
        cxbuilder = New MySqlConnectionStringBuilder
        cxbuilder.Server = mysqlhost
        cxbuilder.Port = mysqlport
        cxbuilder.UserID = mysqluser
        cxbuilder.Password = mysqlpsqwd
        If (idb) Then
            cxbuilder.Database = mysqldb
        End If
        Dim str As String = cxbuilder.GetConnectionString(True)
        Return str
    End Function

    Public Function getDatabaseInfo() As MysqlDbInfo
        Return New MysqlDbInfo
    End Function
    Public Function createTableSql(ByVal table As System.Data.DataTable) As String
        Dim sql = "create table " + table.TableName + " ("
        Dim pk() As DataColumn = table.PrimaryKey
        For Each r As DataColumn In table.Columns
            Dim str As String = ""
            If (pk.Contains(r)) Then
                str = getCreateColumnString(r, True)
            Else
                str = getCreateColumnString(r)
            End If
            sql = sql + str + ","
        Next

        sql = sql.TrimEnd({","c})
        sql = sql + ")" + vbCrLf
        Return sql
    End Function
    Private Function getCreateColumnString(ByVal r As DataColumn, Optional ByVal ispk As Boolean = False)
        Dim str As String = r.ColumnName
        str = str + " " + getSqlType(r)
        str = str + " " + getNotNull(r)
        str = str + " " + getAutoIncrement(r)
        str = str + getDefault(r)
        If (ispk) Then str = str + " primary key"
        Return str
    End Function
    Public Function getSqlType(ByVal r As DataColumn) As String
        Dim str As String = ""
        Select Case Type.GetTypeCode(r.DataType)
            Case TypeCode.String
                If (r.MaxLength <= 0 Or r.MaxLength > 255) Then
                    str = "text"
                Else
                    str = "varchar(" + r.MaxLength.ToString + ")"

                End If
            Case TypeCode.Int32
                str = "int"
            Case TypeCode.Int64
                str = "int"
            Case TypeCode.DateTime
                str = "datetime"
            Case TypeCode.Boolean
                str = "boolean"
            Case TypeCode.Decimal
                str = "decimal (10,3)"
            Case TypeCode.Double
                str = "double (10,3)"
            Case Else

        End Select
        Return str
    End Function
    Private Function getMysqlDbType(ByVal tt As String) As MySqlDbType 
        If (tt = "int") Then Return MySqlDbType.Int32
        If (tt = "varchar") Then Return MySqlDbType.VarChar
        If (tt = "tinyint") Then Return MySqlDbType.Binary
        If (tt = "datetime") Then Return MySqlDbType.DateTime
        If (tt = "double") Then Return MySqlDbType.Double
        Return MySqlDbType.Int32
    End Function
    Private Function getNotNull(ByVal r As DataColumn) As String
        Dim cm As String = ""
        If (Not r.AllowDBNull) Then
            cm = " not null"
        End If
        Return cm
    End Function
    Private Function getDefault(ByVal r As DataColumn) As String
        Dim cm As String = ""
        If (Not IsNothing(r.DefaultValue)) Then
            If (Not r.DefaultValue.Equals(DBNull.Value)) Then
                cm = "default '" + r.DefaultValue + "'"
            End If
        End If
        Return cm
    End Function
    Private Function getAutoIncrement(ByVal r As DataColumn) As String
        Dim cm As String = ""
        If (r.AutoIncrement) Then
            cm = "auto_increment"

        End If
        Return cm
    End Function
    Public Function getAutoIncrementField(ByVal name As String) As DataColumn
        Dim c As New DataColumn(name)
        c.AllowDBNull = False
        c.AutoIncrement = True
        c.DataType = MysqlDtToVb(MySqlDbType.Int32)
        c.Unique = True
        Return c
    End Function
    Public Function getDataColumn(ByVal name As String, ByVal dbt As MySqlDbType) As DataColumn
        Dim c As New DataColumn(name, MysqlDtToVb(dbt))
        Return c
    End Function

    Public Function MysqlDtToVb(ByVal dt As MySqlDbType) As Type
        Dim mt As Type = Nothing
        Select Case dt
            Case MySqlDbType.Binary
                mt = New Boolean().GetType
            Case MySqlDbType.Bit
                mt = New Boolean().GetType
            Case MySqlDbType.Date
                mt = New Date().GetType
            Case MySqlDbType.Decimal
                mt = New Decimal().GetType
            Case MySqlDbType.DateTime
                mt = New Date().GetType
            Case MySqlDbType.Double
                mt = New Double().GetType
            Case MySqlDbType.Float
                mt = New Double().GetType
            Case MySqlDbType.Int32
                mt = New Integer().GetType
            Case Else
                mt = "".GetType
        End Select
        Return mt
    End Function
    Public Function getMySqlServerInfo(ByVal cx As MySqlConnection) As MysqlDbInfo
        Dim dbi As New MysqlDbInfo()
        dbi.ServerAdress = Me.mysqlhost
        dbi.ServerVersion = cx.ServerVersion
        dbi.ConnectionTimeout = cx.ConnectionTimeout
        dbi.Database = Me.mysqldb
        Dim tsc As DataTable = cx.GetSchema
        Dim sc As DataTable = cx.GetSchema("Tables", {Nothing, Me.mysqldb})
        For Each r As DataRow In sc.Rows
            Dim tblname As String = r.Item("TABLE_NAME")
            Dim tble As MysqlTableInfo = New MysqlTableInfo(tblname)
            Dim colnum As ULong = r.Item("TABLE_ROWS")
            tble.ColumnsNumbers = CType(colnum, Integer)
            Dim cdt As DataTable = cx.GetSchema("Columns", {Nothing, Nothing, tblname})
            For Each cr As DataRow In cdt.Rows
                Dim col As MysqlColInfo = New MysqlColInfo(cr.Item("COLUMN_NAME"))
                col.ColumnType = getMysqlDbType(cr.Item("DATA_TYPE"))
                If (cr.Item("COLUMN_KEY") = "PRI") Then col.PrimaryKey = True
                If (cr.Item("EXTRA").ToString.IndexOf("auto") >= 0) Then col.AutoIncrement = True
                tble.addColumn(col)
            Next
            dbi.addTable(tble)

        Next
        Return dbi
    End Function
    Public Function getParameterisedSql(ByRef table As ewindt.DtTableMappingRow, ByRef cols() As ewindt.DtColumnMappingRow) As String
        Dim cm As String = "insert into " + table.mysqltablename + " (recorddate,importedat,"
        Dim param As String = "@recorddate,@importedat,"
        For Each c As ewindt.DtColumnMappingRow In cols
            cm = cm + c.mycolname + ","
            param = param + "@" + c.mycolname + ","
        Next
        cm = cm.TrimEnd({","c})
        param = param.TrimEnd({","c})
        cm = cm + ") values (" + param + ")"
        Return cm
    End Function
End Class
